AppendChunk, GetChunk Method Example

This example illustrates use of the AppendChunk and GetChunk methods to write page-based binary large object (BLOB) data to a remote data source. The code expects a table with a char, text, and image field named Chunks. To create this table, submit the following as an action query against your test database:

CREATE TABLE Chunks (ID integer identity NOT NULL, PName char(10) NULL,
Description TEXT NULL, 
Photo IMAGE NULL)
CREATE UNIQUE INDEX ChunkIDIndex on Chunks(ID)

Once the table is created, you will need to locate one or more .BMP or other suitable graphics images that can be loaded by the PictureBox control.

'
Option Explicit
Dim en As rdoEnvironment
Dim Qd As rdoQuery
Dim Cn As rdoConnection
Dim Rs As rdoResultset
Dim SQL As String
Dim DataFile As Integer, Fl As Long, Chunks As Integer
Dim Fragment As Integer, Chunk() As Byte, I As Integer
Const ChunkSize As Integer = 16384

Private Sub Form_Load()
Set en = rdoEnvironments(0)
Set Cn = en.OpenConnection(dsname:="", _
   Connect:="UID=;PWD=;DATABASE=WorkDB;" _
   & "Driver={SQL Server};SERVER=Betav486", _
   prompt:=rdDriverNoPrompt)
Set Qd = Cn.CreateQuery("TestChunk", "Select * from     
   Chunks Where PName = ?")
End Sub

Private Sub LoadFromFile_Click()
'
'  Locates a file and sets the Filename to this file.
'
With CommonDialog1
   .Filter = "Pictures(*.bmp;*.ico)|*.bmp;*.ico"
   .ShowOpen
   FileName = .FileName
End With
End Sub

Private Sub ReadFromDB_Click()
If Len(NameWanted) = 0 Then _
   NameWanted = InputBox("Enter name wanted", "Animal")
   Qd(0) = NameWanted
   Set Rs = Qd.OpenResultset(rdOpenKeyset, _
   rdConcurRowver)
If Rs Is Nothing Or Rs.Updatable = False Then
   MsgBox "Can’t open or write to result set"
   Exit Sub
End If
If Rs.EOF Then
   MsgBox "Can’t find picture by that name"
   Exit Sub
End If
Description = Rs!Description
DataFile = 1
Open "pictemp" For Binary Access Write As DataFile
Fl = Rs!Photo.ColumnSize
Chunks = Fl \ ChunkSize
Fragment = Fl Mod ChunkSize
ReDim Chunk(Fragment)
Chunk() = Rs!Photo.GetChunk(Fragment)
Put DataFile, , Chunk()
For I = 1 To Chunks
   ReDim Buffer(ChunkSize)
   Chunk() = Rs!Photo.GetChunk(ChunkSize)
   Put DataFile, , Chunk()
Next I
Close DataFile
FileName = "pictemp"
End Sub

Private Sub SaveToDB_Click()
If Len(NameWanted) = 0 Then _
   NameWanted = InputBox("Enter name for this" _
   & " picture", "Animal")
   Qd(0) = NameWanted
   Set Rs = Qd.OpenResultset(rdOpenKeyset, _
   rdConcurRowver)
If Rs Is Nothing Or Rs.Updatable = False Then
   MsgBox "Can’t open or write to result set"
   Exit Sub
End If
If Rs.EOF Then
   Rs.AddNew
   Rs!PName = NameWanted
If Description = "" Then  _
   Description = InputBox("Describe the picture", _ 
   "Don’t care")
   'Rs!Description = Description
Else
   Rs.Edit
End If
DataFile = 1
Open FileName For Binary Access Read As DataFile
Fl = LOF(DataFile)    ' Length of data in file
If Fl = 0 Then Close DataFile: Exit Sub
Chunks = Fl \ ChunkSize
Fragment = Fl Mod ChunkSize
Rs!Photo.AppendChunk Null
ReDim Chunk(Fragment)
Get DataFile, , Chunk()
Rs!Photo.AppendChunk Chunk()
ReDim Chunk(ChunkSize)
For I = 1 To Chunks
   Get DataFile, , Chunk()
   Rs!Photo.AppendChunk Chunk()
Next I
Close DataFile
Rs.Update
End Sub

Private Sub FileName_Change()
Picture1.Picture = LoadPicture(FileName)
End Sub